package com.zhangyj.impl.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.zhangyj.dao.JiaotanDao;
import com.zhangyj.db.DBUtil;
import com.zhangyj.entitis.Jiaotan;
import com.zhangyj.util.Pagination;

public class JiaotanDaoImpl implements JiaotanDao {
	public int getCount(String con) throws SQLException {
		int count = 0;
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT COUNT(*)");
		sql.append(" FROM jiaotan");
		boolean hasCon = con != null && con.length() > 0;
		if (hasCon) {
			sql.append(" WHERE kjmc like?");
		}
		Connection conn = null;
		try {
			conn = DBUtil.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql.toString());
			if (hasCon) {
				ps.setString(1, "%" + con + "%");
			}
			ResultSet rst = ps.executeQuery();
			while (rst.next()) {
				count = rst.getInt(1);
			}
			rst.close();
			ps.close();
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
		return count;
	}

	public List<Jiaotan> getList(Pagination p, String con) throws SQLException {
		List<Jiaotan> list = new ArrayList<Jiaotan>();
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT a.yggh, a.kjmc, a.jllx, a.jlnr, a.jljy, a.jljd, a.jlsj, a.jtid");
		sql.append(" FROM jiaotan a");
		boolean hasCon = con != null && con.length() > 0;
		if (hasCon) {
			sql.append(" WHERE a.kjmc like ?");
		}
		Connection conn = null;
		try {
			conn = DBUtil.getConnection();
			PreparedStatement ps = conn.prepareStatement(p.builderSql(sql.toString(), "jlsj", "a", true));
			if (hasCon) {
				ps.setString(1, "%" + con + "%");
			}
			ResultSet rst = ps.executeQuery();
			while (rst.next()) {
				Jiaotan jt = new Jiaotan();
				builder(jt, rst);
				list.add(jt);
			}
			rst.close();
			ps.close();
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
		return list;
	}

	public Jiaotan loadJiaotan(int jtid) throws SQLException {
		Jiaotan jt = new Jiaotan();
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT a.yggh, a.kjmc, a.jllx, a.jlnr, a.jljy, a.jljd, a.jlsj, a.jtid");
		sql.append(" FROM jiaotan a WHERE a.jtid=?");
		Connection conn = null;
		try {
			conn = DBUtil.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql.toString());
			ps.setInt(1, jtid);
			ResultSet rst = ps.executeQuery();
			if (rst.next()) {
				builder(jt, rst);
			}
			rst.close();
			ps.close();
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
		return jt;
	}

	public Jiaotan delJiaotan(Connection conn, int jtid) throws SQLException {
		Jiaotan jt = new Jiaotan();
		StringBuilder sql = new StringBuilder();
		sql.append("DELETE FROM jiaotan WHERE jtid=?");
		PreparedStatement ps = conn.prepareStatement(sql.toString());
		ps.setInt(1, jtid);
		ps.execute();
		ps.close();
		return jt;
	}

	public void delJiaotan(int jtid) throws SQLException {
		Connection conn = null;
		try {
			conn = DBUtil.getConnection();
			delJiaotan(conn, jtid);
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
	}

	public void saveJiaotan(Jiaotan jt, boolean isUpdate) throws SQLException {
		Connection dbc = null;
		try {
			dbc = DBUtil.getConnection();
			if (isUpdate)
				delJiaotan(dbc, jt.getTjId());
			StringBuilder sql = new StringBuilder();
			sql.append("INSERT INTO jiaotan(");
			sql.append(" yggh, kjmc, jllx, jlnr, jljy, jljd, jlsj, jtid)");
			sql.append(" VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
			PreparedStatement ps = dbc.prepareStatement(sql.toString());
			ps.setString(1, jt.getYggh());
			ps.setString(2, jt.getKjmc());
			ps.setString(3, jt.getJllx());
			ps.setString(4, jt.getJlnr());
			ps.setString(5, jt.getJljy());
			ps.setString(6, jt.getJljd());
			ps.setString(7, jt.getJlsj());
			ps.setInt(8, jt.getTjId());
			ps.execute();
		} catch (SQLException e) {
			throw e;
		} finally {
			if (dbc != null) {
				dbc.close();
			}
		}
	}

	private void builder(Jiaotan jt, ResultSet rst) throws SQLException {
		jt.setTjId(rst.getInt("jtid"));
		jt.setJljd(rst.getString("jljd"));
		jt.setJljy(rst.getString("jljy"));
		jt.setJllx(rst.getString("jllx"));
		jt.setJlnr(rst.getString("jlnr"));
		jt.setJlsj(rst.getString("jlsj"));
		jt.setKjmc(rst.getString("kjmc"));
		jt.setYggh(rst.getString("yggh"));
	}
}
